Release 10.1A: OpenEdge Data Management:
SQL Reference

NEXTVAL

NEXTVAL returns a sequence’s next value. References to NEXTVAL increment the sequence value by the defined increment and return the new value.

Use the following syntax to reference the next value of a sequence:

Syntax

schema.sequence.NEXTVAL

schema

Specifies the schema that contains the sequence. To refer to the next value of a sequence in the schema of another user, you must have SELECT object privilege on the sequence.

sequence

Specifies the name of the sequence whose next value you want.

Note: A statement referencing NEXTVAL for a noncycling sequence returns an error after reaching the maximum value.

Use NEXTVAL in the:

SELECT list of a SELECT statement not contained in a subquery or view.
SELECT list of a subquery in an INSERT statement.
VALUES clause of an INSERT statement.
SET clause of an UPDATE statement.

NEXTVAL cannot be used in:

A query of a view.
A SELECT statement with a GROUP BY clause that references a sequence.
A SELECT statement with an ORDER BY clause that references a sequence.
A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator.
The WHERE clause of a SELECT or UPDATE statement.
The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement.
The condition of a CHECK constraint.

Example

Note: Sequences are not associated with tables. This example provides reference to a table in order to conform to correct OpenEdge SQL syntax.

In the following example, the sequence generator increments the customer sequence and uses its value for a new customer inserted into the table pub.customer:

INSERT INTO pub.customer VALUES (customer_sequence.NEXTVAL,’USA’,’BackCountry Equipment’,’Sugar Hill Road’,’12A’,’Franconia’,’NH’,’03242’,’Dan Egan’,’603-762-2121’,’Kirsten Ulmner’, 10000.00, 500.00,’net 10’, 0,’contact monthly’);